﻿/***********************************
 * Developer: Lio.Huang
 * Date：2018-12-17
 **********************************/

using System;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Text;
using housingsafety.server.Helpers;
using MySql.Data.MySqlClient;

namespace housingsafety.server.Repository.Banana.Extension
{
    /// <summary>
    /// Mysql SqlBulkCopy
    /// </summary>
    public class MySqlBcpStore
    {
        private string _tableName = "";
        private DataTable _myTable = new DataTable();

        /// <summary>
        /// 初始化|initialize
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="tablename"></param>
        public void Init(DbConnection connection, string tablename)
        {
            this._tableName = tablename;
            Init(connection);
        }

        /// <summary>
        /// 初始化|initialize
        /// </summary>
        /// <param name="repository"></param>
        public void Init<T>(Interface.IRepository<T> repository) where T : class, Models.IEntity
        {
            this._tableName = repository.TableName;
            if (repository.DbConnection is DbConnection)
            {
                Init(repository.DbConnection as DbConnection);
            }
            else
            {
                throw new ArgumentException("BCPStore仅Mysql数据库中使用|The BCPStore is only used in Mysql databases.", "repository.DBConnection");
            }
        }

        private void Init(DbConnection connection)
        {
            if (UtilExtenions.ContainsSqlInjection(_tableName))
            {
                throw new ArgumentException("表名存在非法字符");
            }

            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }

            DbProviderFactory dataFactory = DbProviderFactories.GetFactory(connection);
            DbDataAdapter da = dataFactory.CreateDataAdapter();
            DbCommand cmd = connection.CreateCommand();
            cmd.CommandText = $"select * from {_tableName} where 1=0";

            DataSet ds = new DataSet();
            da.Fill(ds);
            _myTable = ds.Tables[0];
            _myTable.Rows.Clear();
        }

        /// <summary>
        /// 数据
        /// </summary>
        public void AddData(object[] objRow)
        {
            _myTable.Rows.Add(objRow);
        }

        /// <summary>
        /// 提交
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="truncateFirst"></param>
        /// <param name="bulkCopyTimeout"></param>
        public void Flush(DbConnection connection, bool truncateFirst, int bulkCopyTimeout = 36000)
        {
            if (UtilExtenions.ContainsSqlInjection(_tableName))
            {
                throw new ArgumentException("表名存在非法字符");
            }

            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }

            try
            {
                if (truncateFirst)
                {
                    DbCommand cmd = connection.CreateCommand();
                    cmd.CommandText = $"truncate table {_tableName}";
                    cmd.ExecuteNonQuery();
                }

                BulkInsert(connection as MySqlConnection, _myTable);

                _myTable.Rows.Clear();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        ///大批量数据插入,返回成功插入行数
        /// </summary>
        /// <param name="conn">数据库连接</param>
        /// <param name="table">数据表</param>
        /// <returns>返回成功插入行数</returns>
        public static int BulkInsert(MySqlConnection conn, DataTable table)
        {
            if (string.IsNullOrEmpty(table.TableName))
            {
                throw new Exception("请给DataTable的TableName属性附上表名称");
            }

            if (table.Rows.Count == 0) return 0;
            int insertCount = 0;
            string tmpPath = Path.GetTempFileName();
            string csv = DataTableToCsv(table);
            File.WriteAllText(tmpPath, csv);
            // MySqlTransaction tran = null;

            {
                try
                {
                    conn.Open();
                    //tran = conn.BeginTransaction();
                    MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
                    {
                        FieldTerminator = ",",
                        FieldQuotationCharacter = '"',
                        EscapeCharacter = '"',
                        LineTerminator = "\r\n",
                        FileName = tmpPath,
                        NumberOfLinesToSkip = 0,
                        TableName = table.TableName,
                    };
                    //bulk.Columns.AddRange(table.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToArray());
                    insertCount = bulk.Load();
                    // tran.Commit();
                }
                catch (MySqlException ex)
                {
                    // if (tran != null) tran.Rollback();
                    throw ex;
                }
            }

            File.Delete(tmpPath);
            return insertCount;
        }

        /// <summary>
        ///将DataTable转换为标准的CSV
        /// </summary>
        /// <param name="table">数据表</param>
        /// <returns>返回标准的CSV</returns>
        private static string DataTableToCsv(DataTable table)
        {
            //以半角逗号（即,）作分隔符，列为空也要表达其存在。
            //列内容如存在半角逗号（即,）则用半角引号（即""）将该字段值包含起来。
            //列内容如存在半角引号（即"）则应替换成半角双引号（""）转义，并用半角引号（即""）将该字段值包含起来。
            StringBuilder sb = new StringBuilder();
            DataColumn colum;
            foreach (DataRow row in table.Rows)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    colum = table.Columns[i];
                    if (i != 0) sb.Append(",");
                    if (colum.DataType == typeof(string) && row[colum].ToString().Contains(","))
                    {
                        sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\"");
                    }
                    else sb.Append(row[colum].ToString());
                }
                sb.AppendLine();
            }


            return sb.ToString();
        }

    }
}
